--========================--
--section: create database--
--========================--

use master;
go

drop database online_news_editorial;
go

create database online_news_editorial;
go

use online_news_editorial;
go


--======================--
--section: create tables--
--======================--

create table Divisions
(
	divId char(6) not null,--pk, div999
	divName varchar(50) not null,
	divDescription varchar(255),
	
	constraint pk_Divisions primary key(divId),
	constraint unq_Divisions unique (divName),
	constraint chk_Divisions check
	(
		divId like 'div[0-9][0-9][0-9]'
	),
);
go



create table Jobs
(
	jobId char(7) not null,--pk, job9999
	jobTitle varchar(50) not null,
	jobDescription varchar(255),
	jobMaxSalary money,
	jobMinSalary money,
	
	constraint pk_Jobs primary key(jobId),
	constraint unq_Jobs unique (jobTitle),
	constraint chk_Jobs check
	(
		jobId like 'job[0-9][0-9][0-9][0-9]'
		and jobMinSalary > 0
		and jobMinSalary <= jobMaxSalary		
	),
);
go



create table Employees
(
	empId char(9) not null,--pk, emp999999
	userId uniqueidentifier,--fk
	empFirstName varchar(50) not null,
	empLastName varchar(50) not null,
	empGender bit not null default 'true',
	empDob date not null,
	empAddress varchar(100) not null default '',
	empPhone varchar(50) not null,
	empDateModify date not null,
	empDateJoin date not null,
	empDateLeave date,
	empSalary money,
	
	constraint pk_Employees primary key(empId),
	constraint fk_Employees_Users foreign key(userId) references aspnet_Users(userId),
	constraint chk_Employees check
	(
		empId like 'emp[0-9][0-9][0-9][0-9][0-9][0-9]'
		and empDateJoin < empDateLeave
	),
);
go

create table JobAssignments
(
	jaId int identity(1,1) not null,--pk
	empId char(9) not null,--fk
	divId char(6) not null,--fk
	jobId char(7) not null,--fk
	jaDateModify date not null,
	jaDateStart date not null,
	jaDateFinish date,
	
	constraint pk_JA primary key(jaId),
	constraint fk_JA_Employees foreign key(empId) references Employees(empId),
	constraint fk_JA_Divisions foreign key(divId) references Divisions(divId),
	constraint fk_JA_Jobs foreign key(jobId) references Jobs(jobId),
	constraint unq_JA_compositekey1 unique(empId, divId, jaDateStart),
	constraint unq_JA_compositekey2 unique(jobId, jaDateStart),
	constraint chk_JA check
	(
		jaDateStart < jaDateFinish
	),
);
go


create table Categories
(
	catId char(7) not null,--pk, cat9999
	catName varchar(50) not null,
	catDescription varchar(255),
	catParentId char(7),--fk
	belongToDivision char(6) not null,--fk
	
	constraint pk_Categories primary key(catId),
	constraint fk_Categories foreign key(catParentId) references Categories(catId),
	constraint fk_Categories_Divisions foreign key(belongToDivision) references Divisions(divId),
	constraint unq_Categories unique (catName),
	constraint chk_Categories check
	(
		catId like 'cat[0-9][0-9][0-9][0-9]'
	),	
);
go



create table News
(
	newsId int identity(1,1) not null,--pk
	catId char(7) not null,--fk
	newsTitle varchar(150) not null,
	newsIntroduceText varchar(1000) not null,
	newsContent varchar(max) not null,
		author char(9) not null,--fk, display in web as a author.
		postingDate datetime not null,
		modificationDate datetime,
			--processLevel int not null default -1,
			processStatus varchar(10) not null,
				verified bit default 'false',
				newsExclusion bit default 'false',
				publisher char(9),--fk
				publishDate datetime,--display in web as a posting date.
				unpublishDate datetime,
	numberOfView int not null default 0,
	
	constraint pk_News primary key(newsId),
	constraint fk_News_Categories foreign key(catId) references Categories(catId),
	constraint fk_News_Employees_As_Author foreign key(author) references Employees(empId),
	constraint fk_News_Employees_As_Publisher foreign key(publisher) references Employees(empId),
	constraint unq_News unique(newsTitle),
	constraint chk_News check
	(
		numberOfView>= 0
		--and processLevel = -1--(-1): writer.
		--or processLevel = 0--(0): editor.
		--or processLevel = 1--(1): approver.

		and processStatus = 'Approved'
		or processStatus = 'Edited'
		or processStatus = 'Posted'
		or processStatus = 'RejectedA'
		or processStatus = 'RejectedE'
	),
);
go


create table NewsVersions
(
	newsId int not null,--pk, fk
	verId int identity(1,1) not null,--pk
	verEditor char(9),--fk
	verSender char(9) not null,--fk
	--verProcessDirection int not null default -1,
	verProcessDirection varchar(10) not null,
	verComments varchar(1000),
	verDate datetime not null,
	
	constraint pk_NewsVersions primary key(newsId, verId),
	constraint fk_NewsVersions_News	foreign key(newsId) references News(newsId),
	constraint fk_NewsVersions_Employees_As_Editor	foreign key(verEditor) references Employees(empId),
	constraint fk_NewsVersions_Employees_As_Sender	foreign key(verSender) references Employees(empId),
	constraint chk_NewsVersions check
	(
		--verProcessDirection = -1--cap tren tra~ bai` xuong.
		--or verProcessDirection = 0--luu bai (hoac nhung chinh sua) ma` ko gui len/xuong
		--or verProcessDirection = 1--cap duoi gui bai` len.
		
		verProcessDirection = 'Reject'--cap tren tra~ bai` xuong.
		or verProcessDirection = 'Post'--cap duoi gui bai` len.
	),
);
go



create table NewsRelated
(
	newsId int,--pk, fk
	newsRelatedId int,--pk, fk
	
	constraint pk_NewsRelated primary key(newsId, newsRelatedId),
	constraint fk_NewsRelated_News_1 foreign key(newsId) references News(newsId),
	constraint fk_NewsRelated_News_2 foreign key(newsRelatedId) references News(newsId),
);
go



create table Media
(
	mId int identity(1,1) not null,--pk
	mName varchar(100) not null,
	mDescription varchar(250),
	mLocation varchar(1000),
	mType varchar(5) not null,
	mDateCreate datetime not null,
	mUploadedBy char(9),--fk
	
	constraint pk_Media primary key(mId),
	constraint fk_Media_Employees foreign key(mUploadedBy) references Employees(empId),
	constraint chk_Media check
	(
		mType like '[vV]ideo'
		or mType like '[aA]udio'
		or mType like '[pP]hoto'
	)
);
go



create table MediaInNews
(
	newsId int,--pk, fk
	mId int,--pk, fk
	
	constraint pk_MediaInNews primary key(newsId, mId),
	constraint fk_MediaInNews_News foreign key(newsId) references News(newsId),
	constraint fk_MediaInNews_Media foreign key(mId) references Media(mId)
);
go



create table Comments
(
	cmtId uniqueidentifier not null default newid(),--pk
	newsId int not null,--fk
	cmtWriter varchar(50)not null,
	cmtContent varchar(max) not null,
	cmtDatePost datetime not null,
	cmtIsIllegal bit not null default 0,
	
	constraint pk_Comments primary key(cmtId),
	constraint fk_Comments_News foreign key(newsId) references News(newsId),
);
go



create table AdvertisementBlocks
(
	blkId char(5) not null,--pk
	blkName varchar(50) not null,
	blkDescription varchar(256),
	blkWidth int not null,
	blkHeight int not null,
	blkActive bit not null default 1,
	
	constraint pk_AdvertisementBlocks primary key(blkId),
);
go



create table AdvertisementContacts
(
	advContId int identity(1,1) not null,--pk
	advContName varchar(50) not null,
	advContCompany varchar(50) not null,
	advContPhone varchar(50) not null,
	
	constraint pk_AdvertisementContact primary key(advContId),
);
go



create table Advertisements
(
	advId int identity(1,1) not null,--pk
	advContId int not null,--fk
	advBlockId char(5) not null,--fk
	advDateCreate date not null,
	advDateModify date not null,
	advDateExpire date not null,
	advActive bit not null default 1,
	advLink varchar(1000) not null,
	advImageLink varchar(1000),
	advAltText varchar(1000),
	advClickCount int not null default 0,
	
	constraint pk_Advertisements primary key(advId),
	constraint fk_Advertisements_AdvertisementContacts foreign key(advContId) references AdvertisementContacts(advContId),
	constraint fk_Advertisements_AdvertisementBlocks foreign key(advBlockId) references AdvertisementBlocks(blkId),
);
go




